﻿using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using Mono.Data.Sqlite;

public class DataBase : MonoBehaviour {
    public static DataBase Instance { get;private set; }
    private void Awake()
    {
        Instance = this;
    }
    private void OnEnable()
    {
        ConnectDB();
    }
    private void Start()
    {
        DontDestroyOnLoad(gameObject);
    }

    internal string NickName { get; set; }

    #region Connect DB
    SqliteConnection connection;
    void ConnectDB()
    {
        string dbpath = "data source=" + Application.streamingAssetsPath + "/Ranking.sqlite";
        connection = new SqliteConnection(dbpath);
    }
    void OpenDB()
    {
        connection.Open();
    }
    void CloseDB()
    {
        connection.Close();
    }
    #endregion

    #region DB operate api
    internal int GetHighestScore()
    {
        int score = 0;
        OpenDB();
        SqliteCommand command = connection.CreateCommand();
        string sql = "select Score from ScoreStorge order by Score desc limit 1";
        command.CommandText = sql;
        SqliteDataReader reader = command.ExecuteReader();
        if (reader.Read())
        {
            score = reader.GetInt32(0);
        }
        reader.Close();
        command.Dispose();
        CloseDB();
        return score;
    }

    internal List<string[]> GetAllRow()
    {
        List<string[]> list = new List<string[]>();
        OpenDB();
        SqliteCommand command = connection.CreateCommand();
        string sql = "select * from ScoreStorge order by Score desc";
        command.CommandText = sql;
        SqliteDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            string[] info = new string[reader.FieldCount];
            for (int i = 0; i < reader.FieldCount; i++)
            {
                info[i] = reader.GetValue(i).ToString();
            }
            list.Add(info);
        }
        reader.Close();
        command.Dispose();
        CloseDB();
        return list;
    }

    internal void SaveScore(string name,string score)
    {
        OpenDB();
        SqliteCommand command = connection.CreateCommand();
        string sql = "insert into ScoreStorge values ('" + name + "','" + score + "')";
        command.CommandText = sql;
        command.ExecuteNonQuery();
        command.Dispose();
        CloseDB();
    }
    #endregion
}
